package com.application.chiettinhdienluc.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.application.chiettinhdienluc.app.MainApplication;
import com.application.chiettinhdienluc.data.GlobalData;
import com.application.chiettinhdienluc.data.SharedPreferencesData;
import com.application.chiettinhdienluc.model.VatTuChiTietModel;


public class VatTuChiTietDataSource {

	private MySQLiteHelper dbHelper;

	private final String  VATTUCHITIET_DB_NAME = "HSO_VATTU_CTIET";

	//table column names
	private static final String BANCTINH_ID = "BANCTINH_ID";
	private static final String MA_VATTU = "MA_VATTU";
	private static final String DON_GIA = "DON_GIA";
	private static final String SO_LUONG = "SO_LUONG";
	private static final String THANH_TIEN = "THANH_TIEN";
	private static final String LAN = "LAN";
	private static final String TRANG_THAI = "TRANG_THAI";
	private static final String MA_DVI_QLY = "MA_DVIQLY";
	private static final String HSO_CHIETTINH_ID = "HSO_CHIETTINH_ID";
	private static final String CHITIET_ID = "CHITIET_ID";

	private final static String TAG = VatTuChiTietDataSource.class
			.getSimpleName();

	public VatTuChiTietDataSource() {
		dbHelper = MySQLiteHelper.getInstance();
	}	

	public long updateValue(VatTuChiTietModel vattuchitiet) {
		long row_id = -1; 
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues cv = new ContentValues();

				cv.put(SO_LUONG, vattuchitiet.getSoLuong());
				cv.put(THANH_TIEN, vattuchitiet.getThanhTien());

				ContentValues values = new ContentValues();

				values.put(HSO_CHIETTINH_ID, 	GlobalData.globalChietTinh.get_Id());
				values.put(MA_VATTU, 			vattuchitiet.getMaVatTu());
				values.put(DON_GIA, 			vattuchitiet.getDonGia());
				values.put(SO_LUONG, 			vattuchitiet.getSoLuong());
				values.put(THANH_TIEN, 			vattuchitiet.getDonGia() * vattuchitiet.getSoLuong());
				values.put(LAN, 				vattuchitiet.getLan());
				values.put(TRANG_THAI, 			vattuchitiet.getTrangthai());
				values.put(MA_DVI_QLY, 			vattuchitiet.getMaDonViQuanLy());
				values.put(BANCTINH_ID, 		vattuchitiet.getBangChietTinhId());
				values.put(CHITIET_ID, 			0);

				if(GlobalData.globalChietTinh.get_Id() <= 0)
					row_id = dbHelper.getDb().insert(VATTUCHITIET_DB_NAME, null, values);
				else
				{
					deleteItemWithQuantityZero(vattuchitiet, false);
					row_id = dbHelper.getDb().insert(VATTUCHITIET_DB_NAME, null, values);
				}

				return row_id;
			}	
		} catch (Exception e) {
		}
		finally{
		}

		return row_id;
	}

	public int deleteItemWithQuantityZero(VatTuChiTietModel vattuchitiet, boolean needToClose) {
		int numRowEffect = 0;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				String whereClause = "HSO_CHIETTINH_ID " + " = ? " + " and " + "MA_DVIQLY" + " = ?" + " and " + "MA_VATTU" + " = ?";
				String[] whereArgs = new String[] { ""+GlobalData.globalChietTinh.get_Id(), SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()), vattuchitiet.getMaVatTu()};
				numRowEffect =  dbHelper.getDb().delete(VATTUCHITIET_DB_NAME, whereClause, whereArgs);
			}
		} catch (Exception e) {
			numRowEffect = 0;
		}
		finally{
		}
		return numRowEffect;
	}

	public int deleteItemByHoSoChietTinhId(String hosoChietTinhId) {
		int numRowEffect = 0;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				String whereClause = "HSO_CHIETTINH_ID " + " = ? " + " and " + "MA_DVIQLY" + " = ? ";
				String[] whereArgs = new String[] {hosoChietTinhId, SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext())};

				numRowEffect = dbHelper.getDb().delete(VATTUCHITIET_DB_NAME, whereClause, whereArgs);
			}
		} catch (Exception e) {
			numRowEffect = 0;
		}
		finally{
		}
		return numRowEffect;
	}

	public ArrayList<VatTuChiTietModel> getSelectedVatTuByHoSoId(long hoSoId)
	{
		ArrayList<VatTuChiTietModel> vatTuChiTiet = new ArrayList<VatTuChiTietModel>();
		Cursor cursor = null;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				cursor = dbHelper.getDb().rawQuery("select vtct.rowid, vtct.ma_vattu, vt.ten_vattu, vt.quy_cach,vtct.don_gia, vtct.so_luong, vtct.thanh_tien " +
						"from HSO_VATTU_CTIET vtct inner join D_vattu  vt on vtct.MA_VATTU = vt.MA_VATTU  " +
						"where vtct.HSO_CHIETTINH_ID = " + hoSoId +
						" and vt.MA_DVIQLY = '" + SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()) + "'", null); 

				if (cursor != null && cursor.getCount() > 0){
					cursor.moveToFirst();

					do {
						VatTuChiTietModel ct = new VatTuChiTietModel();
						ct.setVatTuChiTietId(cursor.getInt(0));
						ct.setMaVatTu(cursor.getString(1));
						ct.setTenVatTu(cursor.getString(2));
						ct.setQuyCach(cursor.getString(3));
						ct.setDonGia(cursor.getDouble(4));
						ct.setSoLuong(cursor.getDouble(5));
						ct.setThanhTien(cursor.getDouble(6));
						ct.setMaDonViQuanLy(SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()));

						vatTuChiTiet.add(ct);

					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
		}
		finally{
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
		}

		return vatTuChiTiet;
	}


	public ArrayList<VatTuChiTietModel> getVatTuChiTietByHoSoId(long hoSoId)
	{
		ArrayList<VatTuChiTietModel> vatTuChiTiet = new ArrayList<VatTuChiTietModel>();
		Cursor cursor = null;
		Cursor cursor1 = null;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				cursor = dbHelper.getDb().
						rawQuery("select vtct.rowid, vtct.ma_vattu, vt.ten_vattu, vt.quy_cach,vtct.don_gia, vtct.so_luong, vtct.thanh_tien " +
								"from HSO_VATTU_CTIET vtct inner join D_vattu  vt on vtct.MA_VATTU = vt.MA_VATTU  " +
								"where vtct.HSO_CHIETTINH_ID = " + hoSoId +
								" and vt.MA_DVIQLY = '" + SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()) + "'", null); 
				int order = 0;
				if (cursor != null && cursor.getCount() > 0) {
					cursor.moveToFirst();
					do {
						VatTuChiTietModel ct = new VatTuChiTietModel();
						ct.setVatTuChiTietId(cursor.getInt(0));
						ct.setMaVatTu(cursor.getString(1));
						ct.setTenVatTu(cursor.getString(2));
						ct.setQuyCach(cursor.getString(3));
						ct.setDonGia(cursor.getDouble(4));
						ct.setSoLuong(cursor.getDouble(5));
						ct.setThanhTien(cursor.getDouble(6));
						ct.setMaDonViQuanLy(SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()));
						ct.setOrder(order);
						ct.setCheck(true);
						vatTuChiTiet.add(ct);

					} while (cursor.moveToNext());
				}

				cursor1 = dbHelper.getDb().
						rawQuery("select rowid, ma_vattu, ten_vattu, quy_cach, don_gia, 0 as so_luong, 0 as thanh_tien " +
								"from D_VATTU " +
								"where su_dung =1 " +
								"and ma_vattu not in (select ma_vattu " +
								"from HSO_VATTU_CTIET " +
								"where HSO_CHIETTINH_ID = " + hoSoId + 
								" and MA_DVIQLY = '" + SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()) + 
								"')", null); 

				ArrayList<VatTuChiTietModel> vatTuChiTiet1 = new ArrayList<VatTuChiTietModel>();
				order = 1;

				if (cursor1 != null && cursor1.getCount() > 0 ) {
					cursor1.moveToFirst();
					do {

						VatTuChiTietModel ct = new VatTuChiTietModel();
						ct.setVatTuChiTietId(cursor1.getInt(0));
						ct.setMaVatTu(cursor1.getString(1));
						ct.setTenVatTu(cursor1.getString(2));
						ct.setQuyCach(cursor1.getString(3));
						ct.setDonGia(cursor1.getDouble(4));
						ct.setSoLuong(cursor1.getDouble(5));
						ct.setThanhTien(cursor1.getDouble(6));
						ct.setBangChietTinhId("");
						ct.setMaDonViQuanLy(SharedPreferencesData.getMaDonViQuanLy(MainApplication.getAppContext()));
						ct.setOrder(order);
						vatTuChiTiet1.add(ct);

					} while (cursor1.moveToNext());
				}

				vatTuChiTiet.addAll(vatTuChiTiet1);
			}
		} catch (Exception e) {
		}
		finally{
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}

			if (cursor1 != null && !cursor1.isClosed()) {
				cursor1.close();
			}
		}

		return vatTuChiTiet;
	}
}